package com.hpeng.sys.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import com.hpeng.sys.model.SysUser;
import com.hpeng.sys.service.Function;
import com.hpeng.sys.vo.Lookup;
import com.hpeng.sys.vo.Menu;
import com.hpeng.sys.vo.FuncProperties;
import com.hpeng.sys.vo.TemplateAuthority;

@Component("function")
public class FunctionImpl implements Function {
	@Resource
	private JdbcTemplate jdbcTemplate;

	public List<Menu> getMenu(String userCode) {
		String sql = "select f.module_id,(select m.module_name from sys_module m where m.module_id=f.module_id) module_name,f.func_name,f.func_page from sys_func f where f.func_code in"
				+ "(select a.func_code from sys_user_authority a where a.user_code=?)"
				+ "and f.status='A' and f.disp_on_scr='Y' order by f.module_id,f.seq";
		RowMapper mapper = new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Menu m = new Menu();
				m.setModule_id(rs.getString("module_id"));
				m.setModule_name(rs.getString("module_name"));
				m.setFunc_name(rs.getString("func_name"));
				m.setFunc_page(rs.getString("func_page"));
				return m;
			}
		};
		List<Menu> rows = jdbcTemplate.query(sql, new Object[] { userCode },
				mapper);
		return rows;
	}

	public String getNavStr(String funcCode) {
		String sql = "select (select m.module_name from sys_module m where m.module_id=f.module_id) || ' >> ' ||f.func_name as mf from sys_func f where f.func_code=?";
		return (String) jdbcTemplate.queryForObject(sql,
				new Object[] { funcCode }, String.class);
	}

	public Long getMaxSeq(String tableName) {
		return jdbcTemplate.queryForLong("select max(seq)+1 from " + tableName);
	}

	public boolean hasSysTemplate(String templateCode) {
		String sql = "select count(1) from sys_template_mst where UPPER(template_name)=UPPER(?)";
		return (jdbcTemplate.queryForInt(sql, new Object[] { templateCode })) > 0;
	}

	public List<Lookup> getLookup(String key) {
		String sql = "SELECT LOOKUP_VALUE,LOOKUP_DESC FROM SYS_LOOKUP_MST WHERE LOOKUP_KEY=? AND STATUS='A' ORDER BY SEQ";
		RowMapper mapper = new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Lookup l = new Lookup();
				l.setValue(rs.getString("LOOKUP_VALUE"));
				l.setDesc(rs.getString("LOOKUP_DESC"));
				return l;
			}
		};
		List<Lookup> rows = jdbcTemplate.query(sql, new Object[] { key },
				mapper);
		return rows;
	}

	public List<Lookup> getLookup(String lookupTable, String valueField,
			String descField) {
		String sql = "select (" + valueField + ") as v, (" + descField
				+ ") as d from " + lookupTable;
		RowMapper mapper = new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Lookup l = new Lookup();
				l.setValue(rs.getString("v"));
				l.setDesc(rs.getString("d"));
				return l;
			}
		};
		List<Lookup> rows = jdbcTemplate.query(sql, mapper);
		return rows;
	}

	public List<FuncProperties> getFuncProperties() {
		StringBuffer sb = new StringBuffer(350);
		sb
				.append("select a.func_code, a.func_name, a.access_right, b.module_name, a.module_id, c.sub_module_name, a.sub_module_id ");
		sb.append("from sys_func a, sys_module b, sys_sub_module c ");
		sb.append("where a.module_id=b.module_id and a.module_id=c.module_id ");
		sb.append("and a.sub_module_id=c.sub_module_id and a.status='A' ");
		sb
				.append("order by b.seq,b.module_id, c.seq,c.sub_module_id, a.seq,a.func_code");

		System.out.println(sb);

		RowMapper mapper = new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				FuncProperties d = new FuncProperties();
				d.setFuncCode(rs.getString("func_code"));
				d.setFuncName(rs.getString("func_name"));
				d.setAccessRight(rs.getString("access_right"));
				d.setModuleName(rs.getString("module_name"));
				d.setModuleId(rs.getString("module_id"));
				;
				d.setSubModuleName(rs.getString("sub_module_name"));
				d.setSubModuleId(rs.getString("sub_module_id"));
				return d;
			}
		};
		List<FuncProperties> rows = jdbcTemplate.query(sb.toString(), mapper);
		return rows;
	}

	public List<TemplateAuthority> getTemplateAuthorities(
			final String templateCode) {
		StringBuffer sb = new StringBuffer(350);
		sb.append("select a.func_code, a.access_right ");
		sb
				.append("from sys_template_authority a, SYS_FUNC b, SYS_MODULE c, SYS_SUB_MODULE d ");
		sb.append("where a.template_code=? and a.func_code=b.func_code ");
		sb.append("and b.module_id=c.module_id and b.status='A' ");
		sb
				.append("and b.sub_module_id=d.sub_module_id AND d.module_id=c.module_id ");
		sb
				.append("order by c.seq,c.module_id, d.seq,d.sub_module_id, b.seq,b.func_code");

		System.out.println(sb);

		RowMapper mapper = new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				TemplateAuthority t = new TemplateAuthority();
				t.setTemplateCode(templateCode);
				t.setFuncCode(rs.getString("func_code"));
				t.setAccessRight(rs.getString("access_right"));
				return t;
			}
		};
		List<TemplateAuthority> rows = jdbcTemplate.query(sb.toString(),
				new Object[] { templateCode }, mapper);
		return rows;
	}

	public Map<String, String> getMapTemplateAuthorities(String templateCode) {
		StringBuffer sb = new StringBuffer(350);
		sb.append("select a.func_code, a.access_right ");
		sb
				.append("from sys_template_authority a, SYS_FUNC b, SYS_MODULE c, SYS_SUB_MODULE d ");
		sb.append("where a.template_code=? and a.func_code=b.func_code ");
		sb.append("and b.module_id=c.module_id and b.status='A' ");
		sb
				.append("and b.sub_module_id=d.sub_module_id AND d.module_id=c.module_id ");
		sb
				.append("order by c.seq,c.module_id, d.seq,d.sub_module_id, b.seq,b.func_code");

		RowMapper mapper = new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				TemplateAuthority t = new TemplateAuthority();
				t.setFuncCode(rs.getString("func_code"));
				t.setAccessRight(rs.getString("access_right"));
				return t;
			}
		};

		Map<String, String> m = new HashMap<String, String>();
		List<TemplateAuthority> l = jdbcTemplate.query(sb.toString(),
				new Object[] { templateCode }, mapper);
		for (TemplateAuthority t : l) {
			m.put(t.getFuncCode(), t.getAccessRight());
		}

		return m;
	}

	public List<String> getAccessRightList() {
		List<String> arList = new ArrayList<String>();
		List<Lookup> li = getLookup("ACCESS_RIGHT");
		for (Lookup l : li) {
			arList.add(l.getValue());
		}
		return arList;
	}

	public void modifyAuthorities(HttpServletRequest request, String userId) {
		String t = request.getParameter("t");
		List<String> arList = getAccessRightList();
		List<FuncProperties> funcList = getFuncProperties();
		String fc = null;
		String smid = null;
		StringBuffer right = new StringBuffer(20);
		jdbcTemplate.update(
				"delete from sys_template_authority where template_code=?",
				new Object[] { t });
		for (FuncProperties f : funcList) {
			fc = f.getFuncCode();
			smid = f.getSubModuleId();
			right.delete(0, right.length());
			for (String ar : arList) {
				if ((request.getParameter(smid + fc + ar) != null)
						&& ("1".equals(request.getParameter(smid + fc + ar)))) {
					right.append(ar);
				}
			}
			if (right.length() > 0) {
				jdbcTemplate
						.update(
								"insert into sys_template_authority (template_code,func_code,access_right) values (?,?,?) ",
								new Object[] { t, fc, right.toString() });
			}
		}
		jdbcTemplate
				.update(
						"delete from sys_user_authority where user_code in(select a.user_code from sys_user_mst a where a.template_code=?)",
						new Object[] { t });
		jdbcTemplate
				.update(
						"insert into sys_user_authority (user_code,func_code,access_right) select a.user_code,b.func_code,b.access_right from sys_user_mst a,sys_template_authority b where a.template_code=b.template_code and b.template_code=?",
						new Object[] { t });
	}

	public void modifyUserAuthorities(String userId) {
		jdbcTemplate.update("delete from sys_user_authority where user_code=?",
				new Object[] { userId });
		jdbcTemplate
				.update(
						"insert into sys_user_authority (user_code,func_code,access_right) select ? as user_code,r.func_code,r.access_right from sys_template_mst m,sys_template_authority r where m.template_code=r.template_code and m.template_code=(select u.template_code from sys_user_mst u where u.user_code=?) and m.status='A'",
						new Object[] { userId, userId });
	}

	public String getMaxStaffId() {
		String sql = "select nvl(lpad(max(lpad(s.staid,5,'0'))+1,5,'0'),'0000') maxVal from staff s";
		return (String) jdbcTemplate.queryForObject(sql, String.class);
	}

	public String getDepIdByParentId(String pId) {
		String sql = "select nvl(lpad(max(x.depid)+1,length(?)+3,'0'),?||'001') as did from department x where x.depparentid=?";
		return (String) jdbcTemplate.queryForObject(sql, new Object[] { pId,
				pId, pId }, String.class);
	}

	public String getAccessRight(String userId, String funcCode) {
		String accessRight = "";
		if (userId != null && (!"".equals(userId.trim())) && funcCode != null
				&& (!"".equals(funcCode.trim()))) {
			String sql = "select t.access_right from sys_user_authority t where t.user_code=? and t.func_code=?";
			accessRight = (String) jdbcTemplate.queryForObject(sql,
					new Object[] { userId, funcCode }, String.class);
		}
		return accessRight;
	}

	public String getAccessRight(Map<String, Object> sessionMap, String funcCode) {
		Object o = sessionMap.get("user");
		String userId = "";
		if (o != null) {
			userId = ((SysUser) o).getUserCode();
		}
		String accessRight = "";
		if (userId != null && (!"".equals(userId.trim())) && funcCode != null
				&& (!"".equals(funcCode.trim()))) {
			String sql = "select t.access_right from sys_user_authority t where t.user_code=? and t.func_code=?";
			accessRight = (String) jdbcTemplate.queryForObject(sql,
					new Object[] { userId, funcCode }, String.class);
		}
		return accessRight;
	}

	public String getAccessRight(HttpServletRequest request, String funcCode) {
		HttpSession session = request.getSession();
		Object o = session.getAttribute("user");
		String userId = "";
		if (o != null) {
			userId = ((SysUser) o).getUserCode();
		}
		String accessRight = "";
		if (userId != null && (!"".equals(userId.trim())) && funcCode != null
				&& (!"".equals(funcCode.trim()))) {
			String sql = "select t.access_right from sys_user_authority t where t.user_code=? and t.func_code=?";
			accessRight = (String) jdbcTemplate.queryForObject(sql,
					new Object[] { userId, funcCode }, String.class);
		}
		return accessRight;
	}

	public int getSonNumber(String table, String parentIdName,
			String parentIdValue) {
		String sql = "select count(*) from " + table + " where " + parentIdName
				+ "=?";
		return jdbcTemplate.queryForInt(sql, new Object[] { parentIdValue });
	}
	
	public String getMainStatusDesc(String statusCode) {
		String sql = "select t.status_desc from prod_status_mst t where t.status_type='P' and t.status_code=? and rownum=1";
		return (String) jdbcTemplate.queryForObject(sql, new Object[] {statusCode}, String.class);
	}
	
	public String getSubStatusDesc(String statusCode, String subCode) {
		String sql = "select t.sub_desc from prod_status_mst t where t.status_type='P' and t.status_code=? and t.sub_code=?";
		return (String) jdbcTemplate.queryForObject(sql, new Object[] {statusCode,subCode}, String.class);
	}
	
	public String getStatusDesc(String statusCode, String subCode) {
		String sql = "select t.status_desc||'-'||t.sub_desc from prod_status_mst t where t.status_type='P' and t.status_code=? and t.sub_code=?";
		return (String) jdbcTemplate.queryForObject(sql, new Object[] {statusCode,subCode}, String.class);
	}
	@SuppressWarnings("unchecked")
	public List<String> getAssetComefrom(){
		String sql = "select come_desc from prod_assetcomefrom_mst";
		return (List<String>)jdbcTemplate.queryForList(sql);
	}
	public void updStatus(String statusCode,String subCode,String... assestId){
		String sql = "update assetcard t set t.ACSTATUS=?||? where t.ACID=?";
		for(String acid:assestId){
		jdbcTemplate.update(sql, new Object[] {statusCode,subCode,acid});
		}
	}
	@SuppressWarnings("unchecked")
	public List<String> getStatusList() {
		String sql = "select status_desc from  prod_status_mst  having count(status_code)>1 group by status_desc";
		return (List<String>)jdbcTemplate.queryForList(sql);
	}

	public String getMainStatusCode(String statusDesc) {
		String sql = "select t.status_code from prod_status_mst t where t.status_type='P' and t.status_desc=? and rownum=1";
		return (String) jdbcTemplate.queryForObject(sql, new Object[] {statusDesc}, String.class);
	}

	public String getMaxSubCode() {
		String sql = "select max(sub_code)+1　from prod_status_mst";
	    return Integer.valueOf(jdbcTemplate.queryForInt(sql)).toString();
	}

	


}